//*** Guidance generated code ***//
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics.Contracts;
using System.Linq;

using ProjectBase.Core;
using ProjectBase.Data;
using ProjectBase.Data.Db;

using Survey.Entity;

namespace Survey.Data.Db
{
    public class ContentTypeDao : BaseDbDao<ContentType, int, ContentTypeFilter, SqlCommand>
    {
        #region members
        const string CommandTextDelete = " DELETE FROM CodeListContentType WHERE ContentTypeId = @ContentTypeId ";

        const string CommandTextInsert =
            " Set @ContentTypeId = ((SELECT Max(ContentTypeId) FROM CodeListContentType) + 1); "
            + " INSERT INTO CodeListContentType (Code, [Name], Suffix, IsVisible, ContentTypeId)  "
            + " VALUES (@Code, @Name, @Suffix, @IsVisible, @ContentTypeId ) ";

        const string CommandTextList = " SELECT * FROM CodeListContentType ";

        const string CommandTextUpdate = " UPDATE CodeListContentType  "
                                         + "  SET Code = @Code "
                                         + "    , Name = @Name "
                                         + "    , Suffix = @Suffix "
                                         + "    , IsVisible = @IsVisible"
                                         + "  FROM CodeListContentType "
                                         + "  WHERE ContentTypeId = @ContentTypeId ";
        #endregion members

        #region implement abstract Get
        public override IEnumerable<ContentType> Find(ContentTypeFilter filter)
        {
            DbCommand.Parameters.Clear();
            // I. assebmle the SQL part
            var commandText = CommandTextList;
            var commandWhere = "$";

            if (filter.CodeExact.IsNotEmpty())
            {
                commandWhere += "AND Code = @CodeExact ";
                DbCommand.Parameters.AddWithValue("@CodeExact", filter.CodeExact);
            }
            if (filter.IdExact.Is())
            {
                commandWhere += "AND ContentTypeId = @ContentTypeId ";
                DbCommand.Parameters.AddWithValue("@ContentTypeId", filter.IdExact.Value);
            }
            if (filter.Code.IsNotEmpty())
            {
                commandWhere += "AND Code LIKE @CodeLike ";
                DbCommand.Parameters.AddWithValue("@CodeLike", filter.Code.Replace("*", "%"));
            }
            if (filter.Name.IsNotEmpty())
            {
                commandWhere += "AND Name LIKE @NameLike ";
                DbCommand.Parameters.AddWithValue("@NameLike", filter.Name.Replace("*", "%"));
            }
            if (filter.IsVisible.Is())
            {
                commandWhere += "AND IsVisible = @IsVisible ";
                DbCommand.Parameters.AddWithValue("@IsVisible", filter.IsVisible.Value);
            }

            var searchCL = filter;
            if (searchCL.Is())
            {
                if (searchCL.Suffix.IsNotEmpty())
                {
                    commandWhere += "AND Suffix LIKE @Suffix ";
                    DbCommand.Parameters.AddWithValue("@Suffix", searchCL.Suffix.Replace("*", "%"));
                }
            }

            if (!commandWhere.IsEqual("$"))
            {
                commandText += commandWhere.Replace("$AND", "WHERE");
            }

            // II. Execute SQL and get TheList
            DbCommand.CommandText = commandText;

            // IV. base will append Ordering and Paging
            return GetListByCriteria(filter);
        }

        /// <summary>
        ///     Iterates the Find - all to find the entity with ID.Equals(id)
        /// </summary>
        /// <param name = "id"></param>
        /// <returns></returns>
        public override ContentType GetById(int id)
        {
            return Find(new ContentTypeFilter { IdExact = id }).SingleOrDefault();
        }
        #endregion implement abstract Get

        #region implement abstract Set
        public override ContentType Add(ContentType entity)
        {
            DbCommand.Parameters.Clear();
            DbCommand.CommandText = CommandTextInsert;
            DbCommand.Parameters.AddWithValue("@Code", entity.Code);
            DbCommand.Parameters.AddWithValue("@Name", entity.Name);
            DbCommand.Parameters.AddWithValue("@Suffix", entity.Suffix);
            DbCommand.Parameters.AddWithValue("@IsVisible", entity.IsVisible);
            var id = new SqlParameter
            {
                SqlDbType = SqlDbType.Int,
                ParameterName = "@ContentTypeId",
                Direction = ParameterDirection.Output,
            };
            DbCommand.Parameters.Add(id);

            DbCommand.ExecuteNonQuery();

            entity.SetId((int)id.Value);
            return entity;
        }

        public override ContentType Update(ContentType entity)
        {
            DbCommand.Parameters.Clear();
            DbCommand.CommandText = CommandTextUpdate;
            DbCommand.Parameters.AddWithValue("@Code", entity.Code);
            DbCommand.Parameters.AddWithValue("@Name", entity.Name);
            DbCommand.Parameters.AddWithValue("@Suffix", entity.Suffix);
            DbCommand.Parameters.AddWithValue("@IsVisible", entity.IsVisible);
            DbCommand.Parameters.AddWithValue("@ContentTypeId", entity.ID);

            DbCommand.ExecuteNonQuery();

            return entity;
        }

        public override void Delete(ContentType entity)
        {
            DbCommand.Parameters.Clear();
            DbCommand.CommandText = CommandTextDelete;
            DbCommand.Parameters.AddWithValue("@ContentTypeId", entity.ID);

            DbCommand.ExecuteNonQuery();
        }
        #endregion implement abstract Set

        #region override Dao services
        /// <summary>
        ///     Sets the ID - to ContentTypeId
        /// </summary>
        /// <param name = "reader"></param>
        /// <returns></returns>
        protected override ContentType CreateEntity(IDataReader reader)
        {
            var entity = base.CreateEntity(reader);
            entity.SetId((int)reader["ContentTypeId"]);
            return entity;
        }
        #endregion override Dao services

        public virtual IEnumerable<ContentType> GetAll()
        {
            return Find(new ContentTypeFilter());
        }
    }
}